﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using Maticsoft.DBUtility;
using DQYT.HTDZ.Model;
using System.Data;
using DQYT.HTDZ.IDAL;

namespace DQYT.HTDZ.OracleDAL
{
    public class BDMenuDAL : IBDMenu
    {
        #region  Method

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int MNU_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tb_sys_menu");
            strSql.Append(" where MNU_ID=:MNU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MNU_ID", OracleType.Int32)			};
            parameters[0].Value = MNU_ID;

            return DbHelperOra.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(BDMenuModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tb_sys_menu(");
            strSql.Append("MNU_ID,MNU_MC,MNU_FID,MNU_SX,MNU_MS,MNU_URL,MNU_BZ,MNU_ZT)");
            strSql.Append(" values (");
            strSql.Append(":MNU_ID,:MNU_MC,:MNU_FID,:MNU_SX,:MNU_MS,:MNU_URL,:MNU_BZ,:MNU_ZT)");
            OracleParameter[] parameters = {
					new OracleParameter(":MNU_ID", OracleType.Int32),
					new OracleParameter(":MNU_MC", OracleType.NVarChar),
					new OracleParameter(":MNU_FID", OracleType.Int32),
					new OracleParameter(":MNU_SX", OracleType.Int32),
					new OracleParameter(":MNU_MS", OracleType.NVarChar),
					new OracleParameter(":MNU_URL", OracleType.NVarChar),
					new OracleParameter(":MNU_BZ", OracleType.NVarChar),
                    new OracleParameter(":MNU_ZT", OracleType.Int32)};
            parameters[0].Value = model.MNU_ID;
            parameters[1].Value = model.MNU_MC;
            parameters[2].Value = model.MNU_FID;
            parameters[3].Value = model.MNU_SX;
            parameters[4].Value = model.MNU_MS;
            parameters[5].Value = model.MNU_URL;
            parameters[6].Value = model.MNU_BZ;
            parameters[7].Value = model.MNU_ZT;

            int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(BDMenuModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tb_sys_menu set ");
            strSql.Append("MNU_MC=:MNU_MC,");
            strSql.Append("MNU_FID=:MNU_FID,");
            strSql.Append("MNU_SX=:MNU_SX,");
            strSql.Append("MNU_MS=:MNU_MS,");
            strSql.Append("MNU_URL=:MNU_URL,");
            strSql.Append("MNU_BZ=:MNU_BZ,");
            strSql.Append("MNU_ZT=:MNU_ZT");
            strSql.Append(" where MNU_ID=:MNU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MNU_MC", OracleType.NVarChar),
					new OracleParameter(":MNU_FID", OracleType.Int32),
					new OracleParameter(":MNU_SX", OracleType.Int32),
					new OracleParameter(":MNU_MS", OracleType.NVarChar),
					new OracleParameter(":MNU_URL", OracleType.NVarChar),
					new OracleParameter(":MNU_BZ", OracleType.NVarChar),
					new OracleParameter(":MNU_ID", OracleType.Int32),
                                           new OracleParameter(":MNU_ZT", OracleType.Int32)};
            parameters[0].Value = model.MNU_MC;
            parameters[1].Value = model.MNU_FID;
            parameters[2].Value = model.MNU_SX;
            parameters[3].Value = model.MNU_MS;
            parameters[4].Value = model.MNU_URL;
            parameters[5].Value = model.MNU_BZ;
            parameters[6].Value = model.MNU_ID;
            parameters[7].Value = model.MNU_ZT;

            int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int MNU_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tb_sys_menu ");
            strSql.Append(" where MNU_ID=:MNU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MNU_ID", OracleType.Int32)			};
            parameters[0].Value = MNU_ID;

            int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string MNU_IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tb_sys_menu ");
            strSql.Append(" where MNU_ID in (" + MNU_IDlist + ")  ");
            int rows = DbHelperOra.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public BDMenuModel GetModel(int MNU_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MNU_ID,MNU_MC,MNU_FID,MNU_SX,MNU_MS,MNU_URL,MNU_BZ,MNU_ZT from tb_sys_menu ");
            strSql.Append(" where MNU_ID=:MNU_ID ");
            OracleParameter[] parameters = {
					new OracleParameter(":MNU_ID", OracleType.Int32)			};
            parameters[0].Value = MNU_ID;

            BDMenuModel model = new BDMenuModel();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["MNU_ID"] != null && ds.Tables[0].Rows[0]["MNU_ID"].ToString() != "")
                {
                    model.MNU_ID = int.Parse(ds.Tables[0].Rows[0]["MNU_ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["MNU_MC"] != null && ds.Tables[0].Rows[0]["MNU_MC"].ToString() != "")
                {
                    model.MNU_MC = ds.Tables[0].Rows[0]["MNU_MC"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MNU_FID"] != null && ds.Tables[0].Rows[0]["MNU_FID"].ToString() != "")
                {
                    model.MNU_FID = int.Parse(ds.Tables[0].Rows[0]["MNU_FID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["MNU_SX"] != null && ds.Tables[0].Rows[0]["MNU_SX"].ToString() != "")
                {
                    model.MNU_SX = int.Parse(ds.Tables[0].Rows[0]["MNU_SX"].ToString());
                }
                if (ds.Tables[0].Rows[0]["MNU_MS"] != null && ds.Tables[0].Rows[0]["MNU_MS"].ToString() != "")
                {
                    model.MNU_MS = ds.Tables[0].Rows[0]["MNU_MS"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MNU_URL"] != null && ds.Tables[0].Rows[0]["MNU_URL"].ToString() != "")
                {
                    model.MNU_URL = ds.Tables[0].Rows[0]["MNU_URL"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MNU_BZ"] != null && ds.Tables[0].Rows[0]["MNU_BZ"].ToString() != "")
                {
                    model.MNU_BZ = ds.Tables[0].Rows[0]["MNU_BZ"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MNU_ZT"] != null && ds.Tables[0].Rows[0]["MNU_ZT"].ToString() != "")
                {
                    model.MNU_ZT = int.Parse(ds.Tables[0].Rows[0]["MNU_ZT"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MNU_ID,MNU_MC,MNU_FID,MNU_SX,MNU_MS,MNU_URL,MNU_BZ,MNU_ZT ");
            strSql.Append(" FROM tb_sys_menu ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tb_sys_menu ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperOra.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.MNU_ID desc");
            }
            strSql.Append(")AS Row, T.*  from tb_sys_menu T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperOra.Query(strSql.ToString());
        }

        

        #endregion  Method

        #region 新添加
        /// <summary>
        /// 获得菜单根据角色ID
        /// </summary>
        /// <param name="JSE_ID">角色ID</param>
        /// <returns>返回菜单数据表</returns>
        public DataTable GetMenu(int JSE_ID, int parentId)
        {
            #region
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat(@"select menu.* from tb_sys_menu menu 
                                  join tb_sys_role_menu jscd on jscd.mnu_id=menu.mnu_id 
                                  where jscd.JSE_ID={0} and menu.mnu_fid={1} order by menu.mnu_sx"
                , JSE_ID, parentId);

            return DbHelperOra.Query(strSql.ToString()).Tables[0];
            #endregion
        }

        /// <summary>
        /// 获得页面标题路径
        /// </summary>
        /// <param name="MNU_ID">菜单ID</param>
        /// <returns></returns>
        public string GetTitlePath(string MNU_ID)
        {
            #region
            string strPath = "";
            DataTable dt = this.GetList("").Tables[0];
            if (dt != null)
            {
                return GetTitlePath(dt, MNU_ID, strPath);
            }
            return "";
            #endregion
        }

        /// <summary>
        /// 获得页面标题目录
        /// </summary>
        /// <param name="dtData">菜单表所有数据</param>
        /// <param name="MNU_ID">当前菜单ID</param>
        /// <param name="TitlePath">当前已有名称</param>
        /// <returns></returns>
        private string GetTitlePath(DataTable dtData, string MNU_ID, string TitlePath)
        {
            #region
            if (dtData != null)
            {
                for (int i = 0; i < dtData.Rows.Count; i++)
                {
                    if (dtData.Rows[i]["MNU_ID"].ToString() == MNU_ID)
                    {
                        if (TitlePath != "")
                        {
                            TitlePath = dtData.Rows[i]["MNU_MC"].ToString() + " >> " + TitlePath;
                        }
                        else
                        {
                            TitlePath = dtData.Rows[i]["MNU_MC"].ToString();
                        }
                        if (dtData.Rows[i]["MNU_FID"].ToString() != "0")
                        {
                            TitlePath = GetTitlePath(dtData, dtData.Rows[i]["MNU_FID"].ToString(), TitlePath);
                        }
                    }
                }
            }
            return TitlePath;
            #endregion
        }

        /// <summary>
        /// 获得标题路径
        /// </summary>
        /// <param name="MNU_ID">菜单ID</param>
        /// <returns></returns>
        public static string GetMenuTitlePath(string MNU_ID)
        {
            #region

            BDMenuDAL dMenu = new BDMenuDAL();
            return dMenu.GetTitlePath(MNU_ID);

            #endregion
        }

        /// <summary>
        /// 获得记录数
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public int GetNum(string strWhere)
        {
            #region
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) as iNum from tb_sys_menu ");
            if (strWhere != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            object obj = DbHelperOra.GetSingle(strSql.ToString());

            return Convert.ToInt32(obj);
            #endregion
        }
        /// <summary>
        /// 获得下一个最大值
        /// </summary>
        /// <returns></returns>
        public int GetNextID()
        {
            return DbHelperOra.GetMaxID("MNU_ID", "tb_sys_menu");
        }
        #endregion
    }
}
